package com.zingrow.web.common.utils;

import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 操作Excel的工具类
 * 
 * @author sy
 *
 */
public class ExcelUtils {
    // 格式化日期
    static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 将数据导出到excel表格,并写入out中
     * 
     * @param titles
     *            excel表格标题
     * @param propertyNames
     *            反射类的成员属性
     * @param datas
     *            要导出的数据
     * @param out
     *            写入的流
     */
    public static void exportExcel(List<String> titles, List<String> propertyNames, List<?> datas, OutputStream out)
            throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("workbook");
        // 创建一个居中的格式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 设置标题行
        HSSFRow row = sheet.createRow(0);
        for (String title : titles) {
            HSSFCell cell = row.createCell(row.getPhysicalNumberOfCells());
            cell.setCellValue(title);
            cell.setCellStyle(style);
        }
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 填充数据
        if (datas != null) {
            for (Object item : datas) {
                if (item != null) {
                    // 创建一行
                    row = sheet.createRow(sheet.getPhysicalNumberOfRows());
                    // 解析数据
                    Map<String, Object> properties = PropertyUtils.describe(item);

                    for (Map.Entry<String, Object> entry : properties.entrySet()) {
                        // 判断是否需要导出此字段
                        String name = entry.getKey();
                        if (!propertyNames.contains(name)) {
                            continue;
                        }
                        // 创建列
                        int index = propertyNames.indexOf(name);
                        HSSFCell cell = row.createCell(index);
                        Object value = entry.getValue();
                        cell.setCellStyle(style);
                        // 填充数据
                        if (value == null)
                            cell.setCellValue("");

                        if (value instanceof String) {
                            cell.setCellValue(value.toString());
                        } else if (value instanceof Integer) {
                            cell.setCellValue((Integer) value);
                        } else if (value instanceof Double) {
                            cell.setCellValue((Double) value);
                        } else if (value instanceof Date) {
                            cell.setCellValue(DateFormatUtils.format((Date) value, "yyyy-MM-dd HH:mm:ss"));
                        } else if (value instanceof Boolean) {
                            cell.setCellValue((Boolean) value);
                        }

                    }
                }
            }
        }
        wb.write(out);
    }
}
